package db;

import model.*;

import java.sql.*;
import java.util.ArrayList;

/**
 * DBVehicle.java
 */
public class DBVehicle
{
	private  Connection con;
	
	/** Creates a new instance of DBVehicle */
	public DBVehicle()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	//Implements the methods from the interface
	// get all Vehicles
	public ArrayList<Vehicle> getAllVehicles(boolean _retriveAssociation)
	{
		return miscWhere("", _retriveAssociation);
	}
	
	//get one Vehicle having the licensePlate
	public Vehicle getVehicleByLicensePlate(String _licensePlate, boolean _retriveAssociation)
	{
		String wClause = " licensePlate = '" + _licensePlate + "'";
		return singleWhere(wClause, _retriveAssociation);
	}
	
	//insert a new Vehicle
	public int insertVehicle(Vehicle _vehicle) throws Exception
	{
		int rc = -1;
		String query="INSERT INTO Vehicle(type, year, color, licensePlate, factoryNr)"
		+"  VALUES('"+
		_vehicle.getType() +"','"+
		_vehicle.getYear() +"','"+
		_vehicle.getColor() +"','"+
		_vehicle.getLicensePlate() +"','"+
		_vehicle.getFactoryNr() +"')";
		
		System.out.println("insert : " + query);
		try
		{// insert new Vehicle +  dependent
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex)
		{
			System.out.println("Vehicle not created");
			throw new Exception ("Vehicle is not inserted correct");
		}
		return(rc);
	}
	
	public int updateVehicle(Vehicle _vehicle)
	{
		Vehicle vehicle  = _vehicle;
		int rc=-1;
		
		String query="UPDATE Vehicle SET "+
			"type ='"+ vehicle.getType() +"', "+
			"year ='"+ vehicle.getYear() +"', "+
			"color ='"+ vehicle.getColor() +"', "+
			"factoryNr ='"+ vehicle.getFactoryNr() +"' "+
			" WHERE licensePlate = '"+ vehicle.getLicensePlate() +"'";
		System.out.println("Update query:" + query);
		try
		{// update vehicle
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			
			stmt.close();
		}//end try
		catch(Exception ex)
		{
			System.out.println("Update exception in Vehicles db: "+ex);
		}
		return(rc);
	}
	
	public boolean deleteVehicle(String _licensePlate)
	{
				boolean succeeded = false;
			
			String query="DELETE FROM Vehicle WHERE licensePlate = '" +
					_licensePlate + "'";
					System.out.println(query);
			try
			{// delete from vehicle
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				stmt.executeUpdate(query);
				succeeded = true;
				stmt.close();
			}//end try
			catch(Exception ex)
			{
				System.out.println("Delete exception in Vehicle db: "+ex);
			}
			return(succeeded);
	}
	
	//private methods
	//michWere is used whenever we want to select more than one Vehicle
	
	
	private ArrayList<Vehicle> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Vehicle> list = new ArrayList<Vehicle>();	
		
		String query =  buildQuery(wClause);
		
		try
		{// read the vehicle from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
		
			while( results.next())
			{
				Vehicle vehicle = new Vehicle();
				vehicle = buildVehicle(results);
				list.add(vehicle);
			}//end while
			stmt.close();
		}//end try
		catch(Exception e)
		{
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	//Singelwhere is used when we only select one Vehicle
	private Vehicle singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Vehicle vehicle = new Vehicle();
		
		String query =  buildQuery(wClause);
		System.out.println(query);
		try
		{// read the Vehicle from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if( results.next())
			{
				vehicle = buildVehicle(results);
				//assocaition is to be build
				stmt.close();
			}
			else
			{//no vehicle was found
				vehicle = null;
			}
		}//end try
		catch(Exception e)
		{
			System.out.println("Query exception: "+e);
		}
		return vehicle;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query="SELECT type, year, color, licensePlate, factoryNr FROM Vehicle";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
		return query;
	}
	//method to build a vehicle object
	private Vehicle buildVehicle(ResultSet results)
	{
		Vehicle vehicle = new Vehicle();
		try
		{
			vehicle.setType(results.getString("type"));
			vehicle.setYear(results.getString("year"));
			vehicle.setColor(results.getString("color"));
			vehicle.setLicensePlate(results.getString("licensePlate"));
			vehicle.setFactoryNr(results.getString("factoryNr"));
		}
		catch(Exception e)
		{
			System.out.println("error in building the Vehicle object");
		}
		return vehicle;
		}
}